﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using LdtJudge.DatabaseHandle;

namespace Ledict.ConditionExtract.DAL
{
    /// <summary>
    /// 法定刑幅度档次表
    /// </summary>
    public class XF_Punishment_GradeDAL
    {
        private string JudgeDoc1;
        public XF_Punishment_GradeDAL()
        {
            JudgeDoc1 = "JudgeDoc";
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(XF_Punishment_GradeModel model)
        {         
            string sql=string.Format(@"insert into XF_Punishment_Grade(
			PG_ID,PG_CaseCauseID,PG_Grade,PG_GradeText,PG_ProvInID)
			values ('{0}','{1}','{2}','{3}',{4})", model.PG_ID,model.PG_CaseCauseID,model.PG_Grade,model.PG_GradeText,model.PG_ProvInID);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string ccId)
        {
            string sql = string.Format(@" delete from XF_Punishment_Grade where PG_ID='{0}' ", ccId);           
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 根据法定刑幅度档次表案由和档次获取主键ID
        /// </summary>
        /// <param name="pg_CaseCauseID"></param>
        /// <param name="pg_Grade"></param>
        /// <returns></returns>
        public string GetPunishmentGradeID(string pg_CaseCauseID,string pg_Grade)
        {
            string sql = string.Format(@" select PG_ID from XF_Punishment_Grade where PG_CaseCauseID = '{0}' and PG_Grade = '{1}' ", pg_CaseCauseID, pg_Grade);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            return Convert.ToString(oBase.ExecuteScalar(oCmd));
            
        }



        /// <summary>
        /// 获取法定刑幅度档次表数据
        /// </summary>
        /// <param name="ccid">节点信息</param>
        /// <returns></returns>
        public DataTable GetPunishmentGrade(string ccid)
        {
            string sql = string.Format(@" SELECT *  FROM XF_Punishment_Grade where PG_CaseCauseID='{0}' order by PG_Grade  ", ccid);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
            var otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            return new DataTable();
        }

        /// <summary>
        /// 修改字典值名称，通过PG_ID
        /// </summary>
        public bool UpdateName(XF_Punishment_GradeModel model)
        {
            string sql = string.Format(@" update XF_Punishment_Grade set PG_CaseCauseID='{0}',PG_Grade='{1}',PG_GradeText='{3}',PG_ProvInID='{4}' where PG_ID='{2}' ", model.PG_CaseCauseID,model.PG_Grade,model.PG_ID,model.PG_GradeText,model.PG_ProvInID);            
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获取法定刑幅度档次表数据
        /// </summary>
        /// <param name="ccid">节点信息</param>
        /// <returns></returns>
        public DataTable GetPunishmentData(string ccid)
        {
            string sql = string.Format(@" select x1.PG_Grade,x1.PG_ID,x1.PG_GradeText,x1.PG_ProvInID,'×'as Del,
            x2.GR_ID,
            case x2.GR_TypeID when 'a0'then '' when 'a1'then '死刑'when 'a2' then '死刑缓期二年执行' when 'a3'then '无期徒刑' when 'a4'then'有期徒刑' when 'a5' then'拘役'when 'a6'then'管制'when '255' then '其他'end as GR_TypeID,
            x2.GR_Minimum,x2.GR_Supreme,x2.GR_Penalunit,
            x3.MA_ID,
            case x3.MA_TypeID when 'a0' then ''when 'a1' then '剥夺政治权利' when 'a2'then'罚金,罚款'when 'a3'then'没收个人部分财产' when 'a4'then'没收个人全部财产' when 'a5'then '剥夺军衔' when 'a6'then '驱逐出境'when 'a7'then '没收财产,没收个人财产' end as MA_TypeID ,
            x3.MA_Minimum,
            x3.MA_Supreme,
            x3.MA_Penalunit,
            case x3.MA_ApplicationID when '1'then '必须单处' when '2'then'选择单处'when '3'then '必须并处'when '4' then '可以并处' when '5'then '并处或单处' end as MA_ApplicationID ,
            case x3.MA_Optional when '0'then '否'when '1'then '是'end as MA_Optional ,
            x3.MA_ProvInID
            From [dbo].[XF_Punishment_Grade] x1
            join [dbo].[XF_Grade_Relation] x2 on x1.PG_ID=x2.PG_ID
            left join [dbo].[XF_Major_Addpenalty] x3 on x2.GR_ID=x3.GM_ID
            where x1.PG_CaseCauseID='{0}'
            order by x1.PG_Grade,x2.GR_TypeID,x3.MA_TypeID  ", ccid);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
            var otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            return new DataTable();
        }


        /// <summary>
        /// 获取法定刑幅度档次表数据
        /// </summary>
        /// <param name="ccid">节点信息</param>
        /// <returns></returns>
        public DataTable GetFDXRelationData(string ccid)
        {
            string sql = string.Format(@"    select a.PR_ID, b.PGR_ID, a.PR_groupID, b.PG_Grade, a.PR_SituationTypeID,'×'as Del from XF_Punishiment_RelatStatus a
join XF_PunishimentGrade_RelatStatus b on a.PR_groupID= b.PR_groupID
where a.PR_CaseCauseID= '{0}' ", ccid);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
            var otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            return new DataTable();
        }

     

    }
}
